using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Instedd.Labs.Geo.Library.Kml;
using Instedd.Labs.Geo.Library.Rss;
using System.Collections.Generic;

namespace Instedd.Labs.GeoBlogging.WebApplication
{
    public static class DataAccess
    {
        public static void AddRssEntry(Guid id, String creator, String title, String description, String link, Guid guid, DateTime pubDate, LatLong latLong)
        {
            string InsertMessageSQL = "INSERT INTO [RssEntries] ([Creator], [Title], [Description], [Link], [Guid], [PubDate], [Id], [LatLong]) VALUES (@creator, @title, @description, @link, @guid, @pubDate, @id, @latLong)";
            Database db = DatabaseFactory.CreateDatabase("GeoBloggingDb");

            using (DbCommand command = db.GetSqlStringCommand(InsertMessageSQL))
            {
                db.AddInParameter(command, "Creator", DbType.String, creator);
                db.AddInParameter(command, "id", DbType.Guid, Guid.NewGuid());
                db.AddInParameter(command, "title", DbType.String, title);
                db.AddInParameter(command, "description", DbType.String, description);
                db.AddInParameter(command, "link", DbType.String, link);
                db.AddInParameter(command, "Guid", DbType.Guid, guid);
                db.AddInParameter(command, "PubDate", DbType.DateTime, pubDate);
                if (latLong != null)
                {
                    db.AddInParameter(command, "LatLong", DbType.String, latLong.ToString());
                }
                else
                {
                    db.AddInParameter(command, "LatLong", DbType.String, null);
                }
                db.ExecuteNonQuery(command);
            }
        }

        public static void AddCommentEntry(Guid itemId, String description, DateTime when, String from)
        {
            string InsertMessageSQL = "INSERT INTO [RssComments] ([Id], [ItemId], [Description], [When], [From]) VALUES (@id, @itemId, @description, @when, @from)";
            Database db = DatabaseFactory.CreateDatabase("GeoBloggingDb");

            using (DbCommand command = db.GetSqlStringCommand(InsertMessageSQL))
            {
                db.AddInParameter(command, "id", DbType.Guid, Guid.NewGuid());
                db.AddInParameter(command, "itemId", DbType.Guid, itemId);
                db.AddInParameter(command, "description", DbType.String, description);
                db.AddInParameter(command, "when", DbType.DateTime, when);
                db.AddInParameter(command, "from", DbType.String, from);
                db.ExecuteNonQuery(command);
            }
        }

        internal static System.Collections.Generic.List<Item> GetAllItems()
        {
            string SelectAllSQL = "SELECT * FROM [RssEntries] ORDER BY [PubDate] DESC";
            Database db = DatabaseFactory.CreateDatabase("GeoBloggingDb");

            System.Collections.Generic.List<Item> items = new System.Collections.Generic.List<Item>();

            using (IDataReader reader = db.ExecuteReader(CommandType.Text, SelectAllSQL))
            {
                while (reader.Read())
                {
                    items.Add(GetItemFromReader(reader));
                }
            }

            return items;
        }

        internal static System.Collections.Generic.List<Comment> GetAllComments(Guid itemGuid)
        {
            string SelectCommentsForItem = "SELECT * FROM [RssComments] WHERE [ItemId] = @ItemId ORDER BY [When] ASC";
            Database db = DatabaseFactory.CreateDatabase("GeoBloggingDb");

            System.Collections.Generic.List<Comment> comments = new System.Collections.Generic.List<Comment>();

            using (DbCommand command = db.GetSqlStringCommand(SelectCommentsForItem))
            {
                db.AddInParameter(command, "ItemId", DbType.Guid, itemGuid);
                using (IDataReader reader = db.ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        comments.Add(GetCommentFromReader(reader));
                    }
                }
                return comments;
            }

            return null;
        }

        public static Item GetItem(Guid id)
        {
            string SelectMessageByIdSQL = "SELECT * FROM [RssEntries] WHERE [Id] = @Id";
            Database db = DatabaseFactory.CreateDatabase("GeoBloggingDb");
            Item item = null;

            using (DbCommand command = db.GetSqlStringCommand(SelectMessageByIdSQL))
            {
                db.AddInParameter(command, "Id", DbType.Guid, id);
                using (IDataReader reader = db.ExecuteReader(command))
                {
                    if (reader.Read())
                    {
                        item = GetItemFromReader(reader);
                    }
                }
            }

            return item;
        }

        internal static void DeleteAllEntries()
        {
            string DeleteAllSQL = "DELETE FROM [RssEntries]";
            Database db = DatabaseFactory.CreateDatabase("GeoBloggingDb");

            db.ExecuteNonQuery(CommandType.Text, DeleteAllSQL);
        }

        private static Item GetItemFromReader(IDataRecord record)
        {
            LatLong latLong = null;
            if (record["LatLong"] != DBNull.Value)
            {
                latLong = LatLong.Parse((String)record["LatLong"]);
            }

            List<Comment> comments = GetAllComments((Guid)record["Id"]);
            Item item = new Item((Guid)record["Id"], (String)record["Creator"], (String)record["Title"], (String)record["Description"], (DateTime)record["PubDate"], null, latLong, comments);
            item.Link = String.Format("http://goldenshadow.clariusconsulting.net/ShowPost.aspx?id={0}", (String)record["Id"].ToString());
            return item;
        }

        private static Comment GetCommentFromReader(IDataRecord record)
        {
            Comment comment = new Comment((Guid)record["ItemId"], (String)record["From"], (String)record["Description"], (DateTime)record["When"]);
            return comment;
        }
    }
}
